﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using V5_Model;

namespace V5_DAL
{
    public class DALProduct
    {
        #region  成员方法

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "K_Product");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from K_Product");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(ModelProduct model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into K_Product(");
            strSql.Append("ID,ChannelID,ClassID,ProductName,ProductKeysWords,ProductCode,ProductBrand,ProductUnit,ProductWeight,ProductMarketPrice,ProductMemberPrice,ProductMinPrice,ProductBigPic,ProductSmallPic,ProductMorePic,ProductCount,ProductFieldType,ProductFields,ProductSingleFields,ProductSizeTable,IsPassed,IsOnSale,IsRecommond,IsOnHot,IsNew,IsSpecialOffer,Package,ShortContent,Content,ProductRelated,Hits,AddDateTime,UpDateTime,XjIDs)");
            strSql.Append(" values (");
            strSql.Append("@ID,@ChannelID,@ClassID,@ProductName,@ProductKeysWords,@ProductCode,@ProductBrand,@ProductUnit,@ProductWeight,@ProductMarketPrice,@ProductMemberPrice,@ProductMinPrice,@ProductBigPic,@ProductSmallPic,@ProductMorePic,@ProductCount,@ProductFieldType,@ProductFields,@ProductSingleFields,@ProductSizeTable,@IsPassed,@IsOnSale,@IsRecommond,@IsOnHot,@IsNew,@IsSpecialOffer,@Package,@ShortContent,@Content,@ProductRelated,@Hits,@AddDateTime,@UpDateTime,@XjIDs)");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@ChannelID", SqlDbType.Int,4),
					new SqlParameter("@ClassID", SqlDbType.Int,4),
					new SqlParameter("@ProductName", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductKeysWords", SqlDbType.NVarChar),
					new SqlParameter("@ProductCode", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductBrand", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductUnit", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductWeight", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductMarketPrice", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductMemberPrice", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductMinPrice", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductBigPic", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductSmallPic", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductMorePic", SqlDbType.NVarChar),
					new SqlParameter("@ProductCount", SqlDbType.Int,4),
					new SqlParameter("@ProductFieldType", SqlDbType.Int,4),
					new SqlParameter("@ProductFields", SqlDbType.NVarChar),
					new SqlParameter("@ProductSingleFields", SqlDbType.NVarChar),
					new SqlParameter("@ProductSizeTable", SqlDbType.NVarChar,3000),
					new SqlParameter("@IsPassed", SqlDbType.Int,4),
					new SqlParameter("@IsOnSale", SqlDbType.Int,4),
					new SqlParameter("@IsRecommond", SqlDbType.Int,4),
					new SqlParameter("@IsOnHot", SqlDbType.Int,4),
					new SqlParameter("@IsNew", SqlDbType.Int,4),
					new SqlParameter("@IsSpecialOffer", SqlDbType.Int,4),
					new SqlParameter("@Package", SqlDbType.Int,4),
					new SqlParameter("@ShortContent", SqlDbType.NChar,10),
					new SqlParameter("@Content", SqlDbType.NVarChar),
					new SqlParameter("@ProductRelated", SqlDbType.NVarChar,300),
					new SqlParameter("@Hits", SqlDbType.Int,4),
					new SqlParameter("@AddDateTime", SqlDbType.DateTime),
					new SqlParameter("@UpDateTime", SqlDbType.DateTime),
					new SqlParameter("@XjIDs", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.ChannelID;
            parameters[2].Value = model.ClassID;
            parameters[3].Value = model.ProductName;
            parameters[4].Value = model.ProductKeysWords;
            parameters[5].Value = model.ProductCode;
            parameters[6].Value = model.ProductBrand;
            parameters[7].Value = model.ProductUnit;
            parameters[8].Value = model.ProductWeight;
            parameters[9].Value = model.ProductMarketPrice;
            parameters[10].Value = model.ProductMemberPrice;
            parameters[11].Value = model.ProductMinPrice;
            parameters[12].Value = model.ProductBigPic;
            parameters[13].Value = model.ProductSmallPic;
            parameters[14].Value = model.ProductMorePic;
            parameters[15].Value = model.ProductCount;
            parameters[16].Value = model.ProductFieldType;
            parameters[17].Value = model.ProductFields;
            parameters[18].Value = model.ProductSingleFields;
            parameters[19].Value = model.ProductSizeTable;
            parameters[20].Value = model.IsPassed;
            parameters[21].Value = model.IsOnSale;
            parameters[22].Value = model.IsRecommond;
            parameters[23].Value = model.IsOnHot;
            parameters[24].Value = model.IsNew;
            parameters[25].Value = model.IsSpecialOffer;
            parameters[26].Value = model.Package;
            parameters[27].Value = model.ShortContent;
            parameters[28].Value = model.Content;
            parameters[29].Value = model.ProductRelated;
            parameters[30].Value = model.Hits;
            parameters[31].Value = model.AddDateTime;
            parameters[32].Value = model.UpDateTime;
            parameters[33].Value = model.XjIDs;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(ModelProduct model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update K_Product set ");
            strSql.Append("ChannelID=@ChannelID,");
            strSql.Append("ClassID=@ClassID,");
            strSql.Append("ProductName=@ProductName,");
            strSql.Append("ProductKeysWords=@ProductKeysWords,");
            strSql.Append("ProductCode=@ProductCode,");
            strSql.Append("ProductBrand=@ProductBrand,");
            strSql.Append("ProductUnit=@ProductUnit,");
            strSql.Append("ProductWeight=@ProductWeight,");
            strSql.Append("ProductMarketPrice=@ProductMarketPrice,");
            strSql.Append("ProductMemberPrice=@ProductMemberPrice,");
            strSql.Append("ProductMinPrice=@ProductMinPrice,");
            strSql.Append("ProductBigPic=@ProductBigPic,");
            strSql.Append("ProductSmallPic=@ProductSmallPic,");
            strSql.Append("ProductMorePic=@ProductMorePic,");
            strSql.Append("ProductCount=@ProductCount,");
            strSql.Append("ProductFieldType=@ProductFieldType,");
            strSql.Append("ProductFields=@ProductFields,");
            strSql.Append("ProductSingleFields=@ProductSingleFields,");
            strSql.Append("ProductSizeTable=@ProductSizeTable,");
            strSql.Append("IsPassed=@IsPassed,");
            strSql.Append("IsOnSale=@IsOnSale,");
            strSql.Append("IsRecommond=@IsRecommond,");
            strSql.Append("IsOnHot=@IsOnHot,");
            strSql.Append("IsNew=@IsNew,");
            strSql.Append("IsSpecialOffer=@IsSpecialOffer,");
            strSql.Append("Package=@Package,");
            strSql.Append("ShortContent=@ShortContent,");
            strSql.Append("Content=@Content,");
            strSql.Append("ProductRelated=@ProductRelated,");
            strSql.Append("Hits=@Hits,");
            strSql.Append("AddDateTime=@AddDateTime,");
            strSql.Append("UpDateTime=@UpDateTime,");
            strSql.Append("XjIDs=@XjIDs");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@ChannelID", SqlDbType.Int,4),
					new SqlParameter("@ClassID", SqlDbType.Int,4),
					new SqlParameter("@ProductName", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductKeysWords", SqlDbType.NVarChar),
					new SqlParameter("@ProductCode", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductBrand", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductUnit", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductWeight", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductMarketPrice", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductMemberPrice", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductMinPrice", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductBigPic", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductSmallPic", SqlDbType.NVarChar,50),
					new SqlParameter("@ProductMorePic", SqlDbType.NVarChar),
					new SqlParameter("@ProductCount", SqlDbType.Int,4),
					new SqlParameter("@ProductFieldType", SqlDbType.Int,4),
					new SqlParameter("@ProductFields", SqlDbType.NVarChar),
					new SqlParameter("@ProductSingleFields", SqlDbType.NVarChar),
					new SqlParameter("@ProductSizeTable", SqlDbType.NVarChar,3000),
					new SqlParameter("@IsPassed", SqlDbType.Int,4),
					new SqlParameter("@IsOnSale", SqlDbType.Int,4),
					new SqlParameter("@IsRecommond", SqlDbType.Int,4),
					new SqlParameter("@IsOnHot", SqlDbType.Int,4),
					new SqlParameter("@IsNew", SqlDbType.Int,4),
					new SqlParameter("@IsSpecialOffer", SqlDbType.Int,4),
					new SqlParameter("@Package", SqlDbType.Int,4),
					new SqlParameter("@ShortContent", SqlDbType.NChar,10),
					new SqlParameter("@Content", SqlDbType.NVarChar),
					new SqlParameter("@ProductRelated", SqlDbType.NVarChar,300),
					new SqlParameter("@Hits", SqlDbType.Int,4),
					new SqlParameter("@AddDateTime", SqlDbType.DateTime),
					new SqlParameter("@UpDateTime", SqlDbType.DateTime),
					new SqlParameter("@XjIDs", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.ChannelID;
            parameters[2].Value = model.ClassID;
            parameters[3].Value = model.ProductName;
            parameters[4].Value = model.ProductKeysWords;
            parameters[5].Value = model.ProductCode;
            parameters[6].Value = model.ProductBrand;
            parameters[7].Value = model.ProductUnit;
            parameters[8].Value = model.ProductWeight;
            parameters[9].Value = model.ProductMarketPrice;
            parameters[10].Value = model.ProductMemberPrice;
            parameters[11].Value = model.ProductMinPrice;
            parameters[12].Value = model.ProductBigPic;
            parameters[13].Value = model.ProductSmallPic;
            parameters[14].Value = model.ProductMorePic;
            parameters[15].Value = model.ProductCount;
            parameters[16].Value = model.ProductFieldType;
            parameters[17].Value = model.ProductFields;
            parameters[18].Value = model.ProductSingleFields;
            parameters[19].Value = model.ProductSizeTable;
            parameters[20].Value = model.IsPassed;
            parameters[21].Value = model.IsOnSale;
            parameters[22].Value = model.IsRecommond;
            parameters[23].Value = model.IsOnHot;
            parameters[24].Value = model.IsNew;
            parameters[25].Value = model.IsSpecialOffer;
            parameters[26].Value = model.Package;
            parameters[27].Value = model.ShortContent;
            parameters[28].Value = model.Content;
            parameters[29].Value = model.ProductRelated;
            parameters[30].Value = model.Hits;
            parameters[31].Value = model.AddDateTime;
            parameters[32].Value = model.UpDateTime;
            parameters[33].Value = model.XjIDs;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from K_Product ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ModelProduct GetModel(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,ChannelID,ClassID,ProductName,ProductKeysWords,ProductCode,ProductBrand,ProductUnit,ProductWeight,ProductMarketPrice,ProductMemberPrice,ProductMinPrice,ProductBigPic,ProductSmallPic,ProductMorePic,ProductCount,ProductFieldType,ProductFields,ProductSingleFields,ProductSizeTable,IsPassed,IsOnSale,IsRecommond,IsOnHot,IsNew,IsSpecialOffer,Package,ShortContent,Content,ProductRelated,Hits,AddDateTime,UpDateTime,XjIDs from K_Product ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            ModelProduct model = new ModelProduct();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ChannelID"].ToString() != "")
                {
                    model.ChannelID = int.Parse(ds.Tables[0].Rows[0]["ChannelID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ClassID"].ToString() != "")
                {
                    model.ClassID = int.Parse(ds.Tables[0].Rows[0]["ClassID"].ToString());
                }
                model.ProductName = ds.Tables[0].Rows[0]["ProductName"].ToString();
                model.ProductKeysWords = ds.Tables[0].Rows[0]["ProductKeysWords"].ToString();
                model.ProductCode = ds.Tables[0].Rows[0]["ProductCode"].ToString();
                model.ProductBrand = ds.Tables[0].Rows[0]["ProductBrand"].ToString();
                model.ProductUnit = ds.Tables[0].Rows[0]["ProductUnit"].ToString();
                model.ProductWeight = ds.Tables[0].Rows[0]["ProductWeight"].ToString();
                model.ProductMarketPrice = ds.Tables[0].Rows[0]["ProductMarketPrice"].ToString();
                model.ProductMemberPrice = ds.Tables[0].Rows[0]["ProductMemberPrice"].ToString();
                model.ProductMinPrice = ds.Tables[0].Rows[0]["ProductMinPrice"].ToString();
                model.ProductBigPic = ds.Tables[0].Rows[0]["ProductBigPic"].ToString();
                model.ProductSmallPic = ds.Tables[0].Rows[0]["ProductSmallPic"].ToString();
                model.ProductMorePic = ds.Tables[0].Rows[0]["ProductMorePic"].ToString();
                if (ds.Tables[0].Rows[0]["ProductCount"].ToString() != "")
                {
                    model.ProductCount = int.Parse(ds.Tables[0].Rows[0]["ProductCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ProductFieldType"].ToString() != "")
                {
                    model.ProductFieldType = int.Parse(ds.Tables[0].Rows[0]["ProductFieldType"].ToString());
                }
                model.ProductFields = ds.Tables[0].Rows[0]["ProductFields"].ToString();
                model.ProductSingleFields = ds.Tables[0].Rows[0]["ProductSingleFields"].ToString();
                model.ProductSizeTable = ds.Tables[0].Rows[0]["ProductSizeTable"].ToString();
                if (ds.Tables[0].Rows[0]["IsPassed"].ToString() != "")
                {
                    model.IsPassed = int.Parse(ds.Tables[0].Rows[0]["IsPassed"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsOnSale"].ToString() != "")
                {
                    model.IsOnSale = int.Parse(ds.Tables[0].Rows[0]["IsOnSale"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsRecommond"].ToString() != "")
                {
                    model.IsRecommond = int.Parse(ds.Tables[0].Rows[0]["IsRecommond"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsOnHot"].ToString() != "")
                {
                    model.IsOnHot = int.Parse(ds.Tables[0].Rows[0]["IsOnHot"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsNew"].ToString() != "")
                {
                    model.IsNew = int.Parse(ds.Tables[0].Rows[0]["IsNew"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsSpecialOffer"].ToString() != "")
                {
                    model.IsSpecialOffer = int.Parse(ds.Tables[0].Rows[0]["IsSpecialOffer"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Package"].ToString() != "")
                {
                    model.Package = int.Parse(ds.Tables[0].Rows[0]["Package"].ToString());
                }
                model.ShortContent = ds.Tables[0].Rows[0]["ShortContent"].ToString();
                model.Content = ds.Tables[0].Rows[0]["Content"].ToString();
                model.ProductRelated = ds.Tables[0].Rows[0]["ProductRelated"].ToString();
                if (ds.Tables[0].Rows[0]["Hits"].ToString() != "")
                {
                    model.Hits = int.Parse(ds.Tables[0].Rows[0]["Hits"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AddDateTime"].ToString() != "")
                {
                    model.AddDateTime = DateTime.Parse(ds.Tables[0].Rows[0]["AddDateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UpDateTime"].ToString() != "")
                {
                    model.UpDateTime = DateTime.Parse(ds.Tables[0].Rows[0]["UpDateTime"].ToString());
                }
                model.XjIDs = ds.Tables[0].Rows[0]["XjIDs"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,ChannelID,ClassID,ProductName,ProductKeysWords,ProductCode,ProductBrand,ProductUnit,ProductWeight,ProductMarketPrice,ProductMemberPrice,ProductMinPrice,ProductBigPic,ProductSmallPic,ProductMorePic,ProductCount,ProductFieldType,ProductFields,ProductSingleFields,ProductSizeTable,IsPassed,IsOnSale,IsRecommond,IsOnHot,IsNew,IsSpecialOffer,Package,ShortContent,Content,ProductRelated,Hits,AddDateTime,UpDateTime,XjIDs ");
            strSql.Append(" FROM K_Product ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,ChannelID,ClassID,ProductName,ProductKeysWords,ProductCode,ProductBrand,ProductUnit,ProductWeight,ProductMarketPrice,ProductMemberPrice,ProductMinPrice,ProductBigPic,ProductSmallPic,ProductMorePic,ProductCount,ProductFieldType,ProductFields,ProductSingleFields,ProductSizeTable,IsPassed,IsOnSale,IsRecommond,IsOnHot,IsNew,IsSpecialOffer,Package,ShortContent,Content,ProductRelated,Hits,AddDateTime,UpDateTime,XjIDs ");
            strSql.Append(" FROM K_Product ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "K_Product";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 1;
            parameters[6].Value = strWhere;
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage", parameters, "ds");
        }

        #endregion  成员方法

        /// <summary>
        /// 分页排序
        /// </summary>
        /// <param name="PageSize">分页大小</param>
        /// <param name="PageIndex">当前分页索引</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="strOrder">排序字段 比如ID</param>
        /// <param name="isDesc">是否降序 0为升序 1 为降序</param>
        /// <returns></returns>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string strOrder, int isDesc)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@OrderfldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@StatfldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),};
            parameters[0].Value = "K_Product";
            parameters[1].Value = "*";//所有字段
            parameters[2].Value = strOrder;//排序字段
            parameters[3].Value = "ID";//统计字段
            parameters[4].Value = PageSize;//分页大小
            parameters[5].Value = PageIndex;//当前分页索引
            parameters[6].Value = 1;//是否返回记录
            parameters[7].Value = isDesc;//升序降序
            parameters[8].Value = strWhere;//查询条件
            return DbHelperSQL.RunProcedure("UP_GetRecordByPageOrder", parameters, "ds");
        }
    }
}
